package com.skivingcloud.report.service.impl;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.skivingcloud.common.exceptions.BusinessException;
import com.skivingcloud.common.utils.JacksonUtil;
import com.skivingcloud.report.config.DataSourceConfig;
import com.skivingcloud.report.config.HikariPoolProperties;
import com.skivingcloud.report.config.InfluxDbDataSourceConfig;
import com.skivingcloud.report.constants.InParamTypeEnum;
import com.skivingcloud.report.constants.ReportConstant;
import com.skivingcloud.report.service.JdbcService;
import com.skivingcloud.report.utils.MybatisTemplateSqlExcutor;
import com.skivingcloud.report.utils.InfluxDBConnection;
import com.skivingcloud.report.utils.VelocityUtil;
import com.zaxxer.hikari.pool.HikariPool;
import org.apache.commons.lang3.StringUtils;
import org.elasticsearch.xpack.sql.jdbc.EsDataSource;
import org.influxdb.dto.QueryResult;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class JdbcServiceImpl implements JdbcService {
    private static final Logger log = LoggerFactory.getLogger(JdbcServiceImpl.class);

    private final HikariPoolProperties hikariPoolProperties;

    public JdbcServiceImpl(HikariPoolProperties hikariPoolProperties) {
        this.hikariPoolProperties = hikariPoolProperties;
    }
    private Object lock = new Object();
    /**
     * 所有数据源的连接池存在map里
     */
    private Map<String, HikariPool> pollMap = new ConcurrentHashMap<>();
    /**
     * influxdb链接测试
     *
     * @param username        用户名
     * @param password        密码
     * @param openurl         url
     * @param database        数据库名
     * @param retentionPolicy retentionPolicy
     * @return 是否
     */
    @Override
    public Boolean influxdbTest(String username, String password, String openurl, String database, String retentionPolicy) {
        InfluxDBConnection influxDBConnection = new InfluxDBConnection(username, password, openurl, database, retentionPolicy);
        return influxDBConnection.ping();
    }

    /**
     * 测试elasticsearch
     *
     * @param jdbcUrl  url
     * @param userName 用户名
     * @param password 密码
     * @return 测试结果
     */
    @Override
    public Boolean esTest(String jdbcUrl, String userName, String password) {
        EsDataSource esDataSource = new EsDataSource();
        esDataSource.setUrl(jdbcUrl);
        try {
            esDataSource.getConnection(StringUtils.isBlank(userName) ? "" : userName,
                    StringUtils.isBlank(password) ? "" : password);
        } catch (Exception e) {
            log.error("连接elasticsearch失败", e);
            return false;
        }
        return true;
    }

    /**
     * 测试关系型数据库连接
     *
     * @param dataSourceConfig 数据库配置
     * @return 结果
     */
    @Override
    public Boolean testRelationalDb(DataSourceConfig dataSourceConfig)  {
        HikariPool hikariDataSource = hikariPoolProperties.dataSource(dataSourceConfig.getJdbcUrl(),
                dataSourceConfig.getUser(), dataSourceConfig.getPassword(), dataSourceConfig.getDriverClass());
        try {
            Connection connection =  hikariDataSource.getConnection();
            connection.close();
            return true;
        } catch (Exception e) {
            log.error("数据库连接测试失败", e);
            return false;
        }
    }

    /**
     * 删除数据库连接池
     *
     * @param id 连接池ID
     */
    @Override
    public void removeJdbcConnectionPool(String id) {
        try {
            HikariPool pool = pollMap.get(id);
            if (pool != null) {
                log.info("remove pool success, datasourceId:{}", id);
                pollMap.remove(id);
            }
        } catch (Exception e) {
            log.error("error", e);
        }
    }

    /**
     * 获取连接
     *
     * @param dataSourceConfig 数据源
     */
    @Override
    public Connection getPooledConnection(DataSourceConfig dataSourceConfig) throws SQLException {
        HikariPool pool = getJdbcConnectionPool(dataSourceConfig);
        return pool.getConnection();
    }

    public HikariPool getJdbcConnectionPool(DataSourceConfig dataSourceConfig) {
        if (!pollMap.containsKey(dataSourceConfig.getId())) {
            synchronized (lock) {
                if (!pollMap.containsKey(dataSourceConfig.getId())) {
                    HikariPool pool = hikariPoolProperties.dataSource(dataSourceConfig.getJdbcUrl(),
                            dataSourceConfig.getUser(), dataSourceConfig.getPassword(),
                            dataSourceConfig.getDriverClass());
                    pollMap.put(dataSourceConfig.getId(), pool);
                    log.info("创建连接池成功：{}", dataSourceConfig.getJdbcUrl());
                }
            }
        }
        return pollMap.get(dataSourceConfig.getId());
    }

    /**
     * 获取influxdb的数据库连接
     * @param dataSourceConfig 配置
     * @return InfluxDBConnection
     */
    @Override
    public InfluxDBConnection getInfluxdbDatasource(final InfluxDbDataSourceConfig dataSourceConfig) {
        final String key = String.format("%s|%s|%s|%s", dataSourceConfig.getUsername(), dataSourceConfig.getPassword(), dataSourceConfig.getOpenurl(),dataSourceConfig.getDatabase())
                .toLowerCase();
        return new InfluxDBConnection(dataSourceConfig.getUsername(), dataSourceConfig.getPassword(), dataSourceConfig.getOpenurl(), dataSourceConfig.getDatabase(), null);
    }

    /**
     *
     * @param dbConnection 数据库连接
     * @param sqlText 查询语句
     * @param dataSourceType 数据源类型
     * @param sqlParams 参数
     */
    @Override
    public List<Map<String, Object>> parseInfluxdbColumns(InfluxDBConnection dbConnection,
                                                          String sqlText,
                                                          String dataSourceType, String sqlParams)
            throws SQLException, JsonProcessingException {
        List<Map<String, Object>> result = new ArrayList<>();
        Map<String, Object> params = new HashMap<>();
        if(StringUtils.isNotEmpty(sqlParams)){
            List<Map<String, Object>> jsonArray = JacksonUtil.jsonToListMap(sqlParams);
            sqlText = processSqlDynamicParam(sqlText);
            for (Map<String, Object> stringObjectMap : jsonArray) {
                String paramDefault = (String) stringObjectMap.get("paramDefault");
                String paramCode = (String) stringObjectMap.get("paramCode");
                Assert.notNull(paramDefault, "当数据库为influxdb时，参数中的默认值必须填写。");
                params.put(paramCode, paramDefault);
            }
        }
        sqlText = VelocityUtil.parseInfluxdb(sqlText, params,"influxdb");
        String sql = preprocessSqlText(sqlText,dataSourceType,params);
        QueryResult queryResult = dbConnection.query(sql);
        if(queryResult.getResults().get(0).getSeries() != null)
        {
            List<String> columns = queryResult.getResults().get(0).getSeries().get(0).getColumns();
            if(!columns.isEmpty())
            {
                for (String s : columns) {
                    Map<String, Object> column = new HashMap<String, Object>();
                    column.put("columnName", s);
                    column.put("name", s);
                    result.add(column);
                }
            }
        }
        return result;
    }

    /**
     * 数据库列
     *
     * @param config   配置
     * @param tplSql   sql
     * @param type     类型
     * @param tplParam 参数
     * @return 列
     */
    @Override
    public List<Map<String, Object>> parseMetaDataColumns(DataSourceConfig config, String tplSql, String type, String tplParam) throws JsonProcessingException {
        List<Map<String, Object>> result = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (ReportConstant.ELASTIC_SEARCH_SQL.equals(type)) {
                EsDataSource esDataSource = new EsDataSource();
                esDataSource.setUrl(config.getJdbcUrl());
                try {
                    stmt =  esDataSource.getConnection(StringUtils.isBlank(config.getUser()) ? "" : config.getUser(),
                            StringUtils.isBlank(config.getPassword()) ? "" : config.getPassword()).createStatement();
                } catch (Exception e) {
                    log.error("连接elasticsearch失败", e);
                }
            }else{
                stmt = getPooledConnection(config).createStatement();
            }
            Map<String, Object> params = new HashMap<>();
            if(StringUtils.isNoneBlank(tplParam)){
                List<Map<String, Object>> jsonArray = JacksonUtil.jsonToListMap(tplParam);
                if(!jsonArray.isEmpty()){
                    tplSql = processSqlDynamicParam(tplSql);
                    for (int i = 0; i < jsonArray.size(); i++) {
                        Map<String, Object> p = JacksonUtil.jsonToMap(jsonArray.get(i).toString());
                        String paramType = (String) p.get("paramType");
                        String paramDefault = (String) p.get("paramDefault");
                        String paramCode = (String) p.get("paramCode");
                        String dateFormat = (String) p.get("dateFormat");
                        if("date".equalsIgnoreCase(paramType)){
                            params.put(paramCode, StringUtils.isNotEmpty(dateFormat) ?
                                    LocalDateTime.now().format(DateTimeFormatter.ofPattern(dateFormat)) :
                                    LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE));
                        }else if("mutiselect".equalsIgnoreCase(paramType) || "multitreeselect".equalsIgnoreCase(paramType)){
                            params.put(paramCode, new ArrayList<Map<String, Object>>());
                        }else {
                            params.put(paramCode, paramDefault);
                        }

                    }
                }
            }
            tplSql = VelocityUtil.parseInfluxdb(tplSql, params,"influxdb");
            rs = stmt.executeQuery(preprocessSqlText(tplSql,type,params));
            final ResultSetMetaData rsMataData = rs.getMetaData();
            final int count = rsMataData.getColumnCount();
            result = new ArrayList<>(count);
            for (int i = 1; i <= count; i++) {
                Map<String, Object> column = new HashMap<String, Object>();
                column.put("columnName", rsMataData.getColumnName(i));
                column.put("name", rsMataData.getColumnLabel(i));
                column.put("dataType", rsMataData.getColumnTypeName(i));
                column.put("width", rsMataData.getColumnDisplaySize(i));
                result.add(column);
            }
        } catch (final SQLException ex) {
            log.error(ex.getMessage(), ex);
            throw new BusinessException("error.sql" + ex.getMessage());
        }
        return result;
    }

    /**
     * 存储过程关系数据库查询列
     *
     * @param config    配置
     * @param sqlText    sql
     * @param type      类型
     * @param inParams  参数
     * @param outParams 参数
     * @return 列
     */
    @Override
    public List<Map<String, Object>> parseProcedureColumns(DataSourceConfig config, String sqlText, String type, List<Map<String, Object>> inParams, List<Map<String, Object>> outParams) {
        List<Map<String, Object>> result = null;
        Connection conn = null;
        CallableStatement cstm = null;
        ResultSet rs = null;
        try {
            conn = getPooledConnection(config);
            if(!sqlText.startsWith("{"))
            {
                sqlText = "{" + sqlText;
            }
            if(!sqlText.endsWith("}"))
            {
                sqlText = sqlText + "}";
            }
            cstm = conn.prepareCall(sqlText); //实例化对象cstm
            if(!inParams.isEmpty())
            {
                Map<String, Object> jsonObject = null;
                for (int i = 0; i < inParams.size(); i++) {
                    jsonObject = inParams.get(i);
                    String paramCode = (String) jsonObject.get("paramCode");
                    //数值类的参数，没有默认值，不输入的时候传0，因为不支持传null
                    if(InParamTypeEnum.INT.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setInt(i+1, Integer.valueOf(String.valueOf(jsonObject.get("paramDefault"))));
                        }else {
                            cstm.setInt(i+1, 0);
                        }
                    }else if(InParamTypeEnum.STRING.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setString(i+1, String.valueOf(jsonObject.get("paramDefault")));
                        }else {
                            cstm.setString(i+1, null);
                        }
                    }else if(InParamTypeEnum.LONG.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setLong(i+1, Long.valueOf(String.valueOf(jsonObject.get("paramDefault"))));
                        }else {
                            cstm.setLong(i+1, 0);
                        }
                    }else if(InParamTypeEnum.DOUBLE.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setDouble(i+1, Double.valueOf(String.valueOf(jsonObject.get("paramDefault"))));
                        }else {
                            cstm.setDouble(i+1, 0);
                        }
                    }else if(InParamTypeEnum.FLOAT.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setFloat(i+1, Float.valueOf(String.valueOf(jsonObject.get("paramDefault"))));
                        }else {
                            cstm.setFloat(i+1, 0);
                        }
                    }else if(InParamTypeEnum.BIGDECIMAL.getCode().equals(jsonObject.get("paramType")))
                    {
                        if(StringUtils.isNotEmpty(jsonObject.get("paramDefault").toString())) {
                            cstm.setBigDecimal(i+1, new BigDecimal(String.valueOf(jsonObject.get("paramDefault"))));
                        }else {
                            cstm.setBigDecimal(i+1, new BigDecimal(0));
                        }
                    }else if(InParamTypeEnum.DATE.getCode().equals(jsonObject.get("paramType")))
                    {
                        String dateFormat = (String) jsonObject.get("dateFormat");
                        if(StringUtils.isBlank(dateFormat)) {
                            dateFormat = "yyyy-MM-dd";
                        }
                        if("YYYY-MM-DD".equals(dateFormat)){
                            dateFormat = "yyyy-MM-dd";
                        }else if("YYYY-MM".equals(dateFormat)){
                            dateFormat = "yyyy-MM";
                        }else if("YYYY-MM-DD HH:mm".equals(dateFormat)){
                            dateFormat = "yyyy-MM-dd HH:mm";
                        }else if("YYYY".equals(dateFormat))
                        {
                            dateFormat = "yyyy";
                        }
                        LocalDateTime defaultDate;
                        try {
                            defaultDate = LocalDateTime.parse(String.valueOf(jsonObject.get("paramDefault")),DateTimeFormatter.ofPattern(dateFormat));
                        } catch (Exception e) {
                            defaultDate = LocalDateTime.now();
                        }
                        cstm.setDate(i+1, java.sql.Date.valueOf(defaultDate.toLocalDate()));
                    }else if(InParamTypeEnum.DATETIME.getCode().equals(jsonObject.get("paramType"))){
                        LocalDateTime defaultDate;
                        try {
                            defaultDate = LocalDateTime.parse(String.valueOf(jsonObject.get("paramDefault")), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                        } catch (Exception e) {
                            defaultDate = LocalDateTime.now();
                        }
                        cstm.setTimestamp(i+1, java.sql.Timestamp.valueOf(defaultDate));
                    }
                }
            }
            if(outParams.isEmpty())
            {	//如果返回值为空，则说明存储过程会返回字段，需要解析
                rs = cstm.executeQuery();
                final ResultSetMetaData rsMataData = rs.getMetaData();
                final int count = rsMataData.getColumnCount();
                result = new ArrayList<>();
                for (int i = 1; i <= count; i++) {
                    Map<String, Object> column = new HashMap<String, Object>();
                    column.put("columnName", rsMataData.getColumnName(i));
                    column.put("name", rsMataData.getColumnLabel(i));
                    column.put("dataType", rsMataData.getColumnTypeName(i));
                    column.put("width", rsMataData.getColumnDisplaySize(i));
                    result.add(column);
                }
            }else {
                //如果返回值不为空，则直接使用返回值
                result = new ArrayList<>();
                for (Map<String, Object> jsonObject : outParams) {
                    Map<String, Object> column = new HashMap<String, Object>();
                    column.put("columnName", jsonObject.get("paramCode"));
                    column.put("name", jsonObject.get("paramCode"));
                    column.put("dataType", jsonObject.get("paramType"));
                    result.add(column);
                }
            }

        } catch (final SQLException ex) {
            throw new BusinessException("error.sql" + ex.getMessage());
        }
        return result;
    }

    /**
     * 处理sql中的参数
     *
     * @param tplSql sql
     * @param params 参数
     * @return 处理后的sql
     */
    @Override
    public String processSqlParams(String tplSql, Map<String, Object> params) {
        if(params != null)
        {
            try {
                tplSql = MybatisTemplateSqlExcutor.parseSql(tplSql, params);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        }
        return tplSql;
    }

    /**
     * 查询获取Influxdb数据
     *
     * @param connection 连接
     * @param sql        sql
     * @return 结果集
     */
    @Override
    public List<Map<String, Object>> getInfluxdbData(InfluxDBConnection connection, String sql) {
        List<Map<String, Object>> result = null;
        try {
            QueryResult queryResult = connection.query(sql);
            if(queryResult.getResults().get(0).getSeries() != null)
            {
                List<String> columns = queryResult.getResults().get(0).getSeries().get(0).getColumns();
                List<List<Object>> values= queryResult.getResults().get(0).getSeries().get(0).getValues();
                if(!values.isEmpty())
                {
                    result = new ArrayList<>();
                    for (List<Object> rowValues : values) {
                        Map<String, Object> map = new HashMap<>();
                        for (int j = 0; j < rowValues.size(); j++) {
                            map.put(columns.get(j), rowValues.get(j));
                        }
                        result.add(map);
                    }
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }finally {
            connection.close();
        }

        return result;
    }

    /**
     * 查询获取数据库数据
     *
     * @param config 配置
     * @param sql    sql
     * @return 结果集
     */
    @Override
    public List<Map<String, Object>> getDatasourceDataBySql(DataSourceConfig config, String sql) throws SQLException {
        Statement stmt = getJdbcConnectionPool(config).getConnection().createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        final ResultSetMetaData rsMataData = rs.getMetaData();
        final int count = rsMataData.getColumnCount();
        List<Map<String, Object>> result = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<String, Object>();
            for(int i = 0; i < count; i++){
                map.put(rsMataData.getColumnLabel(i+1), rs.getObject(rsMataData.getColumnLabel(i+1)));
            }
            result.add(map);
        }
        return result;
    }

    /**
     * 处理sql中的动态参数，去掉空格
     * @param sqlText sql
     */
    public String processSqlDynamicParam(String sqlText){
        List<String> matchGroup = new ArrayList<String>();
        Pattern paramPattern=Pattern.compile("\\$\\s*\\{(.*?)}");
        Matcher parammatcher=paramPattern.matcher(sqlText);
        while(parammatcher.find()){
            matchGroup.add(parammatcher.group());
        }
        if(!(matchGroup.isEmpty())){
            for (String original : matchGroup) {
                String newParam = original.replaceAll(" ", "");
                sqlText = sqlText.replace(original, newParam);
            }
        }
        return sqlText;
    }

    /**
     * 预处理sql，防止sql数据过多,并且处理sql中的参数</p>
     * @param sqlText sql
     * @param dataSourceType 数据源类型
     * @param params 参数
     */
    public static String preprocessSqlText(String sqlText,String dataSourceType,Map<String, Object> params) throws SQLException {
        sqlText = StringUtils.stripEnd(sqlText.trim(), ";");
        Pattern paramPattern=Pattern.compile("\\$\\s*\\{(.*?)}");
        Matcher parammatcher=paramPattern.matcher(sqlText);
        while(parammatcher.find()){
            sqlText = parammatcher.replaceAll("''");
        }
        sqlText = MybatisTemplateSqlExcutor.parseSql(sqlText,params);
        System.err.println("解析后的sql:"+sqlText);
        if(ReportConstant.MYSQL.equals(dataSourceType) || ReportConstant.POSTGRESQL.equals(dataSourceType)
                || ReportConstant.INFLUXDB.equals(dataSourceType))
        {
            final Pattern pattern = Pattern.compile(".*limit\\s+(\\S+?)($|;|\\s+.+)", Pattern.CASE_INSENSITIVE);
            final Matcher matcher = pattern.matcher(sqlText);
            if (matcher.find()) {

            }else {
                sqlText = sqlText + " limit 1";
            }

        }else if(ReportConstant.SQL_SERVER.equals(dataSourceType)){
            final Pattern topPattern = Pattern.compile("top", Pattern.CASE_INSENSITIVE);
            final Matcher topMatcher = topPattern.matcher(sqlText);
            if(!topMatcher.find())
            {
                Pattern pattern = Pattern.compile("distinct", Pattern.CASE_INSENSITIVE);
                Matcher matcher = pattern.matcher(sqlText);
                if(matcher.find()) {

                }else {
                    pattern = Pattern.compile("select", Pattern.CASE_INSENSITIVE);
                    matcher = pattern.matcher(sqlText);
                    if (matcher.find()) {
                        sqlText = matcher.replaceFirst("select top 1");
                    }
                }

            }
        }else if(ReportConstant.ORACLE.equals(dataSourceType)){
            sqlText = "SELECT * FROM (" + sqlText + ") WHERE ROWNUM = 1";
        }

        return sqlText;
    }
}
